#! python
# -*- coding: UTF-8 -*-
"""
@Project ：meiduo-server 
@File    ：vernier.py
@IDE     ：PyCharm 
@Author  ：zhangyiheng
@Date    ：2024/12/6 13:36 
@Describe：orm游标 Mysql 直接执行自定义SQL
目的是当遇到复杂连表sql时习惯先写sql在拼接，省去拼接那一步并且减少try 给出友好提示和日志记录
"""
from collections import namedtuple

from django.db import connection, connections, DatabaseError
import logging

from common.enums.status_code_enum import StatusCodeEnum
from config.exception.common_exception import BaseDatabaseError

log = logging.getLogger('django')
"""
Python 标准库的 collections.namedtuple()。
namedtuple 是一个类元组对象，可以通过属性查找来访问其包含的字段；也能通过索引和迭代。
结果都是不可变的，但能通过字段名或索引访问，这很实用:
[Result(id=54360982, parent_id=None), Result(id=54360880, parent_id=None)]

常见问题：
    1.字段名重复
    2.表名错误
"""

# 返回多条result对象集合
# [Result(id=54360982, parent_id=None), Result(id=54360880, parent_id=None)]
def list(sql, *args):
    """
    Return all rows from a cursor as a namedtuple.
    Assume the column names are unique.
    sql：
    args：占位符参数
    """
    try:
        with connection.cursor() as cursor:
            log.info(f"sql:{sql}"
                     f"参数:{args}")
            cursor.execute(sql, *args)
            desc = cursor.description
            nt_result = namedtuple("result", [col[0] for col in desc])
            res = [nt_result(*row) for row in cursor.fetchall()]
    except Exception as e:
        log.error("自定义list游标错误:", e)
        raise BaseDatabaseError(StatusCodeEnum.DB_ERR)
    return res


# 查询单个数据
def get_one(sql, *args):
    """
    Return all rows from a cursor as a namedtuple.
    Assume the column names are unique.
    """
    try:
        res = list(sql + "LIMIT 2", *args)
        if res and len(res) == 2:
            log.error("期望查询一条，但出现了多条。")
            raise BaseDatabaseError(StatusCodeEnum.DB_ERR)
    except Exception as e:
        log.error("数据库游标「查询单个数据」错误: ", e)
        raise BaseDatabaseError(StatusCodeEnum.DB_ERR)
    return res[0]


# 根据表名获取数据库总条数
def get_count(tableName) -> int:
    """
    :param tableName: 表名
    :return: 数据库总条数
    """
    try:
        with connection.cursor() as cursor:
            log.info(f"tableName :{tableName}")
            cursor.execute(f"select count(1) from {tableName}")
            count: int = cursor.fetchone()[0]
    except Exception as e:
        log.error(f"没有查到这个数据库：{tableName}或其他错误：", e)
        raise BaseDatabaseError(StatusCodeEnum.DB_NOT_FAND_ERR)

    return count